<?php

class System_Data_Adapter_Pdo
{
    /**
     * PDO:: beginTransaction
     * PDO:: commit
     * PDO:: _ _ construct
     * PDO:: errorCode
     * PDO:: errorInfo
     * PDO:: exec
     * PDO:: getAttribute
     * PDO:: getAvailableDrivers
     * PDO:: lastInsertId
     * PDO:: prepare
     * PDO:: query
     * PDO:: quote
     * PDO:: rollBack
     * PDO:: setAttribute
     */

    /**
     * Database connection.
     *
     * @var object
     */
    private $_connection = null;

    /**
     * Query sql and parameters
     *
     * @var array
     */
    private $_query = array();

    private $_currentDbName = null;

    /**
     * Convert table field name to property
     *
     * @var boolean
     */
    private $_fieldToProperty = false;

    /**
     * Singleton instance
     *
     * Marked only as protected to allow extension of the class. To extend,
     * simply override {@link getInstance()}.
     *
     * @var System_Database_Abstract
     */
    protected static $_instance = null;

    /**
     * Singleton instance
     *
     * @return System_Database_Abstract
     */
    public static function getInstance()
    {
        if (null === self::$_instance) {
            self::$_instance = new self();
        }

        return self::$_instance;
    }

    public function __construct($dsn, $user = null, $password = null)
    {
        $this->setConnection($dsn, $user, $password);
    }

    /**
     * Connect to a database server.
     * @return void
     */
    public function setConnection($dsn, $user, $password)
    {
        try {
            $this->_connection = new PDO($dsn, $user, $password);
            $this->_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (PDOException $e) {
            throw new Exception($e->getMessage(), $e->getCode());
        }

        if (!$this->_connection) {
            throw new Exception('Database Connecting error.');
        }

        $this->_connection->exec("SET NAMES 'utf8'");
    }

    /**
     * Check if the database has a connection.
     *
     * @return boolean
     */
    public function hasConnection()
    {
        if ($this->_connection) return true;
        return false;
    }

    /**
     * Get database connection.
     *
     * @return object
     */
    public function getConnection()
    {
        if ($this->_connection) {
            return $this->_connection;
        } else {
            throw new Exception('Database connection error.', 500);
        }
    }

    /**
     * Disconnects from a database.
     *
     * @return void
     */
    public function disconnect() {
        $this->_connection = null;
    }

    public function getCurrentDbName()
    {
        if(empty($this->_currentDbName)) {
            throw new Exception('Undefined database name');
        }
        return $this->_currentDbName;
    }

    public function setCurrentDbName($dbName)
    {
        $this->_currentDbName = $dbName;
    }

    /**
     * Quotes a string for use in a query.
     *
     * @return string
     */
    public function quote($string)
    {
        return $this->_connection->quote($string);
    }

    public function getStartTime()
    {
        return array_sum(explode(' ', microtime()));
        //return microtime(true);
    }

    public function getProcessTime($startTime)
    {
        return (array_sum(explode(' ', microtime())) - $startTime);
        //return microtime(true) - $startTime;
    }

    public function setQueryInfo($sql, $params, $startTime)
    {
        $processTime = $this->getProcessTime($startTime);
        $this->_query[] = array(
            'sql' => $sql,
            'params' => $params,
            'time' => $processTime
        );
    }

    /**
     * Executes a prepared statement.
     *
     * @return array
     */
    public function query($sql, $params = null)
    {
        $startTime = $this->getStartTime();
        $sth = $this->_connection->prepare($sql);
        if (is_array($params)) {
            $sth->execute($params);
        } else {
            $sth->execute();
        }
        $rowCount = $sth->rowCount();
        $this->setQueryInfo($sql, $params, $startTime);
        return $rowCount;
    }

    /**
     * Get the next row from a result set.
     *
     * @return array
     */
    public function getRow($sql, $params = null)
    {
        $startTime = $this->getStartTime();
        $sql = $this->_applyLimit($sql, 0, 1);
        $sth = $this->_connection->prepare($sql);
        if (is_array($params)) {
            $sth->execute($params);
        } else {
            $sth->execute();
        }

        $result = $sth->fetch(PDO::FETCH_ASSOC);
        $sth->closeCursor();

        if($this->_fieldToProperty && $result) {
            $result = $this->fieldsToProperties($result);
        }
        $this->setQueryInfo($sql, $params, $startTime);
        return $result;
    }

    /**
     * Returns an array containing all of the result set rows.
     *
     * @return array
     */
    public function getRows($sql, $params = null, $limit = null, $offset = null)
    {
        $startTime = $this->getStartTime();
        $sql = $this->_applyLimit($sql, $limit, $offset);
        $sth = $this->_connection->prepare($sql);

        if (is_array($params)) {
            $sth->execute($params);
        } else {
            $sth->execute();
        }

        $result = $sth->fetchAll(PDO::FETCH_ASSOC);
        $sth->closeCursor();

        if($this->_fieldToProperty && $result) {
            $result = $this->fieldsToProperties($result, true);
        }
        $this->setQueryInfo($sql, $params, $startTime);
        return $result;
    }

    /**
     * Get one result
     *
     * @return array
     */
    public function get($table, $conditions = null, $fields = null)
    {
        $fields = $this->_parseSelectFields($fields);
        $sql = sprintf('SELECT %s FROM %s', $fields, $table);
        $and = ' ';
        $params = array();
        if ($conditions) {
            $sql .= ' WHERE';
            foreach($conditions as $key => $value) {
                $sql .= $and . $key . ' = :' . $key;
                $and = ' AND ';
                $params[$key] = $value;
            }
        }

        $result = $this->getRow($sql, $params);
        return $result;
    }

    /**
     * Convert query fields to sql string
     *
     * @return string
     */
    private function _parseSelectFields($fields)
    {
        if($fields && is_array($fields)) {
            return implode(', ', $fields);
        }
        return '*';
    }

    /**
     * Returns an array containing all of the result set rows
     *
     * @return array
     */
    public function find($table, $conditions = null, $fields = null, $order = null, $limit = null, $offset = null)
    {
        $fields = $this->_parseSelectFields($fields);
        $sql = sprintf('SELECT %s FROM %s', $fields, $table);
        $and = ' ';
        $params = array();

        if ($conditions) {
            $sql .= ' WHERE';
            foreach($conditions as $key => $value) {
                if(is_array($value)) {
                    $value = array_filter(array_unique($value));
                    if($value) {
                        $sql .= $and . $key . ' IN (' . trim(str_repeat('?,', count($value)), ',') . ')';
                        $and = ' AND ';
                        foreach($value as $v) {
                            $params[] = $v;
                        }
                    } else {
                        $sql .= $and . ' 1 != 1';
                    }
                } else {
                    $sql .= $and . $key . ' = ?';
                    $and = ' AND ';
                    $params[] = $value;
                }
            }
        }

        if ($order) {
            $sql .= ' ORDER BY';
            $and3 = ' ';
            foreach($order as $key => $value) {
                $sql .= $and3 . ' ' . $key . ' ' . strtoupper($value);
                $and3 = ', ';
            }
        }

        $result = $this->getRows($sql, $params, $limit, $offset);
        return $result;
    }

    public function search($table, $fields = null, $order = null, $limit = null, $offset = null)
    {
        $sql = 'SELECT * FROM ' . $table;
        $and = ' ';
        $params = array();

        if ($fields) {
            $sql .= ' WHERE';
            foreach($fields as $field => $value) {
                $sql .= $and . $field . ' LIKE ?';
                $and = ' AND ';
                $params[] = '%' . $value . '%';
            }
        }

        if ($order) {
            $sql .= ' ORDER BY';
            $and3 = ' ';
            foreach($order as $key => $value) {
                $sql .= $and3.' '.$key.' '.strtoupper($value);
                $and3 = ', ';
            }
        }

        $result = $this->getRows($sql, $params, $limit, $offset);
        return $result;
    }

    /**
     * Count the result set rows.
     *
     * @return int
     */
    public function count($table, $conditions = null)
    {
        $sql = 'SELECT COUNT(*) AS num FROM ' . $table;
        $and = ' ';
        $params = array();

        if ($conditions) {
            $sql .= ' WHERE';
            foreach($conditions as $key => $value) {
                if(is_array($value)) {
                    $value = array_filter(array_unique($value));
                    if($value) {
                        $sql .= $and . $key . ' IN (' . trim(str_repeat('?,', count($value)), ',') . ')';
                        $and = ' AND ';
                        foreach($value as $v) {
                            $params[] = $v;
                        }
                    } else {
                        $sql .= $and . ' 1 != 1';
                    }
                } else {
                    $sql .= $and . $key . ' = ?';
                    $and = ' AND ';
                    $params[] = $value;
                }
            }
        }

        $result = $this->getRow($sql, $params);
        if( empty($result) ) return 0;
        return intval($result['num']);
    }

    /**
     * Inserts into the specified table values associated to the key fields
     *
     * @param string The table name
     * @param array An associative array of fields and values to insert
     * @return boolean True on success, false if not
     * <code>$this->create( 'mytable', array( 'fieldName' => 'value' ) );</code>
     */
    public function insert($table, $fields)
    {
        if (empty($fields)) {
            throw new Exception('The submitted data was invalid.');
        }

        ksort($fields);

        $query = "INSERT INTO {$table} ( ";
        $comma = '';

        $params = array();
        foreach($fields as $field => $value) {
            if(empty($field)) {
                throw new Exception('Empty field name for insert');
            }
            $query.= $comma . $field;
            $comma= ', ';
            $params[]= $value;
        }
        $query.= ' ) VALUES ( ' . trim( str_repeat( '?,', count( $fields ) ), ',' ) . ' );';

        $this->query($query, $params);

        return $this->_connection->lastInsertId();
    }

    /**
     * function update
     * Updates any record that matches the specific criteria
     * A new row is inserted if no existing record matches the criteria
     *
     * @param string Table to update
     * @param array Associative array of field values to set
     * @param array Associative array of field values to match
     * @return boolean True on success, false if not
     *
     * <code>Record::update( 'mytable', array( 'fieldName' => 'newvalue' ), array( 'fieldName' => 'value' ) );</code>
     */
    public function update($table, $fields, $conditions)
    {
        if (empty($fields) or !is_array($fields)) {
            throw new Exception('The submitted data was invalid.');
        }

        ksort($fields);
        ksort($conditions);

        $conditionValues= array();
        foreach($conditions as $conditionName => $conditionValue) {
            if(is_numeric($conditionName)) {
                $conditionValues[$conditionValue]= $fields[$conditionValue];
            } else {
                $conditionValues[$conditionName]= $conditionValue;
            }
        }

        if($this->exists($table, $conditionValues)) {
            $sql = "UPDATE {$table} SET";
            $params = array();
            $comma = '';
            foreach($fields as $fieldName => $fieldValue) {
                if(empty($fieldName)) {
                    throw new Exception('Empty field name for update');
                }
                $sql .= $comma . " {$fieldName} = ?";
                $params[] = $fieldValue;
                $comma = ' ,';
            }
            $sql .= ' WHERE 1 = 1 ';

            foreach($conditions as $conditionName => $conditionValue) {
                $sql .= "AND {$conditionName} = ? ";
                $params[] = $conditionValue;
            }
            return $this->query($sql, $params);
        } else {
            throw new Exception('Database update error, record not found.');
        }
    }


    /**
     * Deletes any record that matches the specific criteria
     *
     * @param string Table to delete from
     * @param array Associative array of field values to match
     * @return boolean True on success, false if not
     * <code>DB::delete('mytable', array('fieldName' => 'value'));</code>
     */
    public function delete($table, $conditions)
    {
        $sql = "DELETE FROM {$table} WHERE 1 = 1 ";
        $values = array();
        foreach ($conditions as $conditionName => $conditionValue) {
            $sql .= "AND {$conditionName} = ? ";
            $values[] = $conditionValue;
        }

        return $this->query($sql, $values);
    }

    /**
     * Checks for a record that matches the specific criteria.
     *
     * @param string Table to check
     * @param array Associative array of field values to match
     * @return boolean True if any matching record exists, false if not
     * <code>DB::exists('mytable', array('fieldName' => 'value'));</code>
     */
    public function exists($table, $conditions)
    {
        $sql = "SELECT 1 as c FROM {$table} WHERE 1=1 ";

        $values= array();
        foreach($conditions as $conditionName => $conditionValue) {
            $sql .= " AND {$conditionName} = ? ";
            $values[] = $conditionValue;
        }
        $result= $this->getRow($sql, $values);
        return ($result !== false);
    }

    public function distinct($property)
    {
    }

    /**
     * Prepares a statement for execution and returns a statement object.
     *
     * @return object
     */
    public function prepare($string)
    {
        return $this->_connection->prepare($string);
    }

    /**
     * Retrieves the ID generated for an AUTO_INCREMENT column by the previous INSERT query.
     *
     * @return int|FALSE  int on success or FALSE on failure
     */
    public function lastInsertId()
    {
        return $this->_connection->lastInsertId();
    }

    /**
     * Begins a transaction (if supported).
     *
     * @param  string  optinal savepoint name
     * @return void
     * @throws Exception
     */
    public function beginTransaction($savepoint = null)
    {
        if (!$this->_connection->beginTransaction()) {
            $err = $this->_connection->errorInfo();
            throw new Exception("SQLSTATE[$err[0]]: $err[2]", $err[1]);
        }
    }

    /**
     * Commits statements in a transaction.
     *
     * @param  string  optinal savepoint name
     * @return void
     * @throws Exception
     */
    public function commit($savepoint = null)
    {
        if (!$this->_connection->commit()) {
            $err = $this->_connection->errorInfo();
            throw new Exception("SQLSTATE[$err[0]]: $err[2]", $err[1]);
        }
    }

    /**
     * Rollback changes in a transaction.
     *
     * @param  string  optinal savepoint name
     * @return void
     * @throws Exception
     */
    public function rollBack($savepoint = null)
    {
        if (!$this->_connection->rollBack()) {
            $err = $this->_connection->errorInfo();
            throw new Exception("SQLSTATE[$err[0]]: $err[2]", $err[1]);
        }
    }

    /**
     * Returns the connection resource.
     * @return PDO
     */
    public function getResource()
    {
        return $this->_connection;
    }

    /**
     * Injects LIMIT/OFFSET to the SQL query.
     *
     * @param  string &$sql  The SQL query that will be modified.
     * @param  int $limit
     * @param  int $offset
     * @return void
     */
    private function _applyLimit($sql, $limit, $offset)
    {
        if (empty($limit) && empty($offset)) return $sql;

        switch ($this->_connection->getAttribute(PDO::ATTR_DRIVER_NAME)) {
            case 'mysql':
                $sql .= ' LIMIT ' . ($limit < 0 ? '18446744073709551615' : (int) $limit)
                    . ($offset > 0 ? ', ' . (int) $offset : '');
                break;

            case 'pgsql':
                if ($limit >= 0) $sql .= ' LIMIT ' . (int) $limit;
                if ($offset > 0) $sql .= ' OFFSET ' . (int) $offset;
                break;

            case 'sqlite':
            case 'sqlite2':
            case 'oci':
                $sql .= ' LIMIT ' . $limit . ($offset > 0 ? ' OFFSET ' . (int) $offset : '');
                break;

            case 'odbc':
            case 'mssql':
                if ($offset < 1) {
                    $sql = 'SELECT TOP ' . (int) $limit . ' * FROM (' . $sql . ')';
                    break;
                }
                // intentionally break omitted
            default:
                throw new Exception('PDO or driver does not support applying limit or offset.');
        }
        return $sql;
    }

    /**
     * Get query error code.
     *
     * @return integer
     */
    public function errorCode()
    {
        return $this->_connection->errorCode();
    }

    /**
     * Get query error info.
     *
     * @return string
     */
    public function errorInfo()
    {
        return $this->_connection->errorInfo();
    }

    /**
     * Convert `example_id` to `exampleId`
     *
     * @return string
     */
    public function fieldToProperty($field)
    {
        $key = str_replace(' ', '', ucwords('_' . str_replace('_', ' ', strtolower($field))));
        return substr($key, 1);
    }

    /**
     * Convert fields to properties
     *
     * @return array
     */
    public function fieldsToProperties($fields, $muti = false)
    {
        if(empty($fields)) {
            return array();
        }

        $data = array();
        if($muti) {
            $fieldsMap = array();
            foreach($fields as $k => $v) {
                foreach($v as $name => $value) {
                    if(array_key_exists($name, $fieldsMap)) {
                        $data[$k][$fieldsMap[$name]] = $value;
                        continue;
                    }
                    $field = $this->fieldToProperty($name);
                    $fieldsMap[$name] = $field;
                    $data[$k][$field] = $value;
                }
            }
            return $data;
        }

        foreach($fields as $name => $value) {
            $field = $this->fieldToProperty($name);
            $data[$field] = $value;
        }

        return $data;
    }

    /**
     * Convert exampleId to example_id
     *
     * @return string
     */
    public function propertyToField($property)
    {
        $field = preg_replace('/([A-Z])([a-z]+)/', '_\1\2', $property);
        return strtolower($field);
    }

    /**
     * Set field to property
     *
     * @return void
     */
    public function setFieldToProperty($value = true)
    {
        $this->_fieldToProperty = $value;
    }

    /**
     * Migrate table structure changes to database
     * @param String $table Table name
     * @param Array $fields Fields and their attributes as defined in the mapper
     */
    public function migrateTable($table, array $fields)
    {
        // Get current fields for table
        $tableExists = false;
        $tableColumns = $this->getColumns($table);

        if($tableColumns) {
            $tableExists = true;
        }
        if($tableExists) {
            // Determine missing or changed columns, if any
            // var_dump($tableColumns);

            // Update table
            $this->migrateTableUpdate($table, $fields);
        } else {
            // Create table
            $this->migrateTableCreate($table, $fields);
        }
    }

    /**
     * Execute a CREATE TABLE command
     */
    public function migrateTableCreate($table, array $formattedFields)
    {
        /*
            STEPS:
            * Use fields to get column syntax
            * Use column syntax array to get table syntax
            * Run SQL
        */

        // Prepare fields and get syntax for each
        $columnsSyntax = array();
        foreach($formattedFields as $fieldName => $fieldInfo) {
            $columnsSyntax[$fieldName] = $this->migrateSyntaxFieldCreate($fieldName, $fieldInfo);
        }

        // Get syntax for table with fields/columns
        $sql = $this->migrateSyntaxTableCreate($table, $formattedFields, $columnsSyntax);

        $this->query($sql);
        return true;
    }

    /**
     * Execute a CREATE TABLE command
     */
    public function __createTable($table, array $formattedFields)
    {
        /*
            STEPS:
            * Use fields to get column syntax
            * Use column syntax array to get table syntax
            * Run SQL
        */

        // Prepare fields and get syntax for each
        $columnsSyntax = array();
        foreach($formattedFields as $fieldName => $fieldInfo) {
            $columnsSyntax[$fieldName] = $this->migrateSyntaxFieldCreate($fieldName, $fieldInfo);
        }

        // Get syntax for table with fields/columns
        $sql = $this->migrateSyntaxTableCreate($table, $formattedFields, $columnsSyntax);

        $this->query($sql);
        return true;
    }

    /**
     * Execute an ALTER/UPDATE TABLE command
     */
    public function migrateTableUpdate($table, array $formattedFields)
    {
        /*
            STEPS:
            * Use fields to get column syntax
            * Use column syntax array to get table syntax
            * Run SQL
        */

        // Prepare fields and get syntax for each
        $tableColumns = $this->getColumnsForTable($table, $this->getCurrentDbName());
        $updateFormattedFields = array();
        foreach($tableColumns as $fieldName => $columnInfo) {
            if(isset($formattedFields[$fieldName])) {
                // TODO: Need to do a more exact comparison and make this non-mysql specific
                if (
                        $this->_fieldTypeMap[$formattedFields[$fieldName]['type']] != $columnInfo['DATA_TYPE'] ||
                        $formattedFields[$fieldName]['default'] !== $columnInfo['COLUMN_DEFAULT']
                    ) {
                    $updateFormattedFields[$fieldName] = $formattedFields[$fieldName];
                }

                unset($formattedFields[$fieldName]);
            }
        }

        $columnsSyntax = array();

        foreach($updateFormattedFields as $fieldName => $fieldInfo) {
            $columnsSyntax[$fieldName] = $this->migrateSyntaxFieldUpdate($fieldName, $fieldInfo, false);
        }

        // Get syntax for table with fields/columns
        if ( !empty($columnsSyntax) ) {
            $sql = $this->migrateSyntaxTableUpdate($table, $formattedFields, $columnsSyntax);

            return $this->query($sql);
        }
        return true;
    }

    public function getColumns($table)
    {
        if(!$this->tableExists($table)) {
            return false;
        }

        $sql = "SHOW COLUMNS FROM ".$table."";
        return $this->getRows($sql);
    }

    public function tableExists($table)
    {
        $sql = "SHOW TABLES LIKE '{$table}'";
        return $this->query($sql);
    }

    /**
     * Truncate a database table
     * Should delete all rows and reset serial/auto_increment keys to 0
     */
    public function truncateTable($table)
    {
        $sql = "TRUNCATE TABLE " . $table;

        return $this->query($sql);
    }

    /**
     * Drop a database table
     * Destructive and dangerous - drops entire table and all data
     */
    public function dropTable($table)
    {
        $sql = "DROP TABLE IF EXISTS " . $table;

        return $this->query($sql);
    }

    /**
     * Create a database
      * Will throw errors if user does not have proper permissions
     */
    public function createDatabase($database)
    {
        $sql = "CREATE DATABASE " . $database;

        return $this->query($sql);
    }

    /**
     * Drop a database table
     * Destructive and dangerous - drops entire table and all data
     * Will throw errors if user does not have proper permissions
     */
    public function dropDatabase($database)
    {
        $sql = "DROP DATABASE " . $database;

        return $this->query($sql);
    }

    public function debug()
    {
        $str = '<table border="0" class="data-table" width="100%">';
        $str .= '<tr><th>ID</th><th>SQL</th><th>Parameters</th><th>Time</th></tr>';
        $totalTime = 0;
        foreach($this->_query as $key => $query) {
            $tr = '<tr><td>%s</td><td>%s</td><td>%s</td><td>%f</td></tr>';
            $no = $key + 1; $params = var_export($query['params'], true);
            $str .= sprintf($tr, $no, $query['sql'], $params, $query['time']);
            $totalTime += $query['time'];
        }
        $str .= sprintf('<tr><td colspan="4" align="right">Total: %f</td></tr>', $totalTime);
        $str .= '</table>';
        return $str;
    }

}
